/**
 * @Author: SevDaisy十七散人 
 * @Date: 2020-07-07 09:42:42
 */
package zyy.control;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import zyy.model.BeanOrders;
import zyy.model.BeanUsers;
import zyy.model.BeanViewOrder;

import zyy.util.BusinessException;
import zyy.util.DBUtil;
import zyy.util.DbException;
import zyy.util.Bus;

public class OrderManager {
  public BeanOrders add(BeanUsers currentUser) throws DbException, BusinessException {
    BeanOrders result = null;
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "INSERT INTO Orders(UserID,oValue,oRealValue,creatTime,oStatus) VALUES(?,0,0,?,'新建订单')";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, currentUser.getUserID());
      long timeNow = System.currentTimeMillis();
      pst.setTimestamp(2, new java.sql.Timestamp(timeNow));
      pst.executeUpdate();
      sql = "select OrderID from Orders where UserID=? and oStatus='新建订单'";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, currentUser.getUserID());
      ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        result = new BeanOrders();
        result.setOrderID(rs.getInt(1));
      } else {
        throw new BusinessException("建立新订单失败");
      }
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public void del(BeanViewOrder o) throws BusinessException {
    // 查出 订单 当前状态
    // 若 骑手已接单、订单已送达 则 businessEX("订单已派送\n不可退单")
    // 若 已确认收货，则单独删除订单数据，即可(订单相应的评论要不要删除？不要了，就让评论绑定用户而不是绑定订单吧)
    // 若 等待送单，则，删除订单并一一还原库存数据
    String oStatus;
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "SELECT oStatus FROM Orders WHERE OrderID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, o.getOrderID());
      java.sql.ResultSet rs = pst.executeQuery();
      rs.next();
      oStatus = rs.getString(1);
      switch (oStatus) {
        case "骑手已接单":
        case "订单已送达":
          throw new BusinessException("订单已派送\n不可退单");
        case "等待送单":
          // 更新库存
          // 先查出每个商品ID、订单数量和库存，再更新库存
          List<Integer> ps = new ArrayList<Integer>();
          List<Double> cs = new ArrayList<Double>();
          List<Double> ss = new ArrayList<Double>();
          sql = "SELECT op.ProductID, op.opCnt FROM OrderProjects op WHERE OrderID = ?";
          PreparedStatement pst1 = conn.prepareStatement(sql);
          pst1.setInt(1, o.getOrderID());
          rs = pst1.executeQuery();
          while (rs.next()) {
            Integer p = new Integer(rs.getInt(1));
            Double c = new Double(rs.getDouble(2));
            ps.add(p);
            cs.add(c);
          }
          sql = "SELECT p.store FROM Product p WHERE ProductID=?";
          pst1 = conn.prepareStatement(sql);
          for (int i = 0; i < ps.size(); i++) {
            pst1.setInt(1, ps.get(i));
            rs = pst1.executeQuery();
            if (rs.next()) {
              Double s = new Double(rs.getDouble(1));
              ss.add(s);
            } else {
              throw new BusinessException("查无此商品");
            }
          }
          sql = "UPDATE Product SET store=? WHERE ProductID=?";
          pst1 = conn.prepareStatement(sql);
          for (int i = 0; i < ps.size(); i++) {
            pst1.setInt(2, ps.get(i));
            pst1.setDouble(1, ss.get(i) + cs.get(i));
            pst1.executeUpdate();
          }
          pst1.close();
          // break;
        case "已确认收货":
          sql = "DELETE FROM Orders WHERE OrderID=?";
          pst = conn.prepareStatement(sql);
          pst.setInt(1, o.getOrderID());
          pst.executeUpdate();
          break;
        default:
          throw new BusinessException("异常的订单状态：" + oStatus);
      }
      rs.close();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new BusinessException(e1.getMessage());
      }
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }

  }

  public void removeByOrderID(int OrderID) throws DbException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      System.out.println("Ready to Delete OrderID " + OrderID);
      String sql = "DELETE FROM Orders WHERE OrderID=? or oStatus='新建订单'";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      pst.executeUpdate();
      sql = "DELETE FROM OrderProjects WHERE OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      pst.executeUpdate();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new DbException(e1);
      }
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void sendToRider(BeanOrders x) throws BusinessException {
    // 检查订单内是否只有一个商家
    // SELECT COUNT(DISTINCT MID) FROM OrderProjects WHERE OrderID=?
    // 由此拒绝提交订单或者更新订单的商家信息
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "SELECT COUNT(DISTINCT MID) FROM OrderProjects WHERE OrderID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, x.getOrderID());
      ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        if (rs.getInt(1) > 1) {
          throw new BusinessException("每个订单只应该在一家商店购买，请检查订单内商品");
        } else {
          sql = "update Orders set MID=? where OrderID=?";
          pst = conn.prepareStatement(sql);
          pst.setInt(1, rs.getInt(1));
          pst.setInt(2, x.getOrderID());
          pst.executeUpdate();
          sql = "select AddressID from OrderProjects o,Merchant m " + " where o.MID=m.MID and o.OrderID=?";
          pst = conn.prepareStatement(sql);
          pst.setInt(1, x.getOrderID());
          rs = pst.executeQuery();
          rs.next();
          int AddressID = rs.getInt(1);
          sql = "update Orders set mAddressID=? where OrderID=?";
          pst = conn.prepareStatement(sql);
          pst.setInt(1, AddressID);
          pst.setInt(2, x.getOrderID());
          pst.executeUpdate();
          x.setmAddressID(AddressID);
        }
      } else {
        throw new BusinessException("空订单不可结算");
      }
      sql = "update Orders set oStatus=?,uAddressID=? where OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setString(1, "等待送单");
      pst.setInt(2, Bus.currentAddress.getAddressID());
      pst.setInt(3, x.getOrderID());
      pst.executeUpdate();
      x.setoStatus("等待送单");
      x.setuAddressID(Bus.currentAddress.getAddressID());
      // 更新库存
      // 先查出每个商品ID、订单数量和库存，再更新库存
      List<Integer> ps = new ArrayList<Integer>();
      List<Double> cs = new ArrayList<Double>();
      List<Double> ss = new ArrayList<Double>();
      sql = "SELECT op.ProductID, op.opCnt FROM OrderProjects op WHERE OrderID = ?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, x.getOrderID());
      rs = pst.executeQuery();
      while (rs.next()) {
        Integer p = new Integer(rs.getInt(1));
        Double c = new Double(rs.getDouble(2));
        ps.add(p);
        cs.add(c);
      }
      sql = "SELECT p.store FROM Product p WHERE ProductID=?";
      pst = conn.prepareStatement(sql);
      for (int i = 0; i < ps.size(); i++) {
        pst.setInt(1, ps.get(i));
        rs = pst.executeQuery();
        if (rs.next()) {
          Double s = new Double(rs.getDouble(1));
          ss.add(s);
        } else {
          throw new BusinessException("查无此商品");
        }
      }
      sql = "UPDATE Product SET store=? WHERE ProductID=?";
      pst = conn.prepareStatement(sql);
      for (int i = 0; i < ps.size(); i++) {
        pst.setInt(2, ps.get(i));
        pst.setDouble(1, ss.get(i) - cs.get(i));
        pst.executeUpdate();
      }
      pst.close();
      conn.commit();
      // System.out.println(x);
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      e.printStackTrace();
      try {
        conn.rollback();
        System.out.println("回滚成功");
        throw new BusinessException("出现SQL异常，触发回滚\n回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new BusinessException("出现SQL异常，触发回滚\n回滚失败！！！");
      }
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }

  }

  public List<BeanViewOrder> load_V_All(BeanUsers x) throws BusinessException {
    List<BeanViewOrder> result = new ArrayList<BeanViewOrder>();
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql;
      java.sql.PreparedStatement pst;
      if (x != null) {
        sql = "select OrderID,RiderID,rName,MID,mName,uTelphone,planTime,oValue,oRealValue,oStatus,mAddr,uAddr "
            + " from ViewUserOrder " + " where UserID=? ";
        pst = conn.prepareStatement(sql);
        pst.setInt(1, x.getUserID());
      } else {
        sql = "select OrderID,RiderID,rName,MID,mName,uTelphone,planTime,oValue,oRealValue,oStatus,mAddr,uAddr,UserID "
            + " from ViewUserOrder " + " ORDER BY RiderID";
        pst = conn.prepareStatement(sql);
      }
      java.sql.ResultSet rs = pst.executeQuery();
      while (rs.next()) {
        BeanViewOrder p = new BeanViewOrder();
        p.setUserID(x == null ? rs.getInt(13) : x.getUserID());
        p.setOrderID(rs.getInt(1));
        p.setRiderID(rs.getInt(2));
        p.setrName(rs.getString(3));
        p.setMID(rs.getInt(4));
        p.setmName(rs.getString(5));
        p.setuTelphone(rs.getString(6));
        if (rs.getString(7) != null)
          p.setPlanTime(Bus.sdf.parse(rs.getString(7)));
        else
          p.setPlanTime(null);
        p.setoValue(rs.getDouble(8));
        p.setoRealValue(rs.getDouble(9));
        p.setoStatus(rs.getString(10));
        p.setmAddr(rs.getString(11));
        p.setuAddr(rs.getString(12));
        result.add(p);
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ParseException e) {
      e.printStackTrace();
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public void riderChoose(BeanViewOrder x) throws DbException, BusinessException {
    // 检查相应 Orders 元组的 oStatus 应为 “等待送单”
    // 更改相应 Orders 元组的 oRiderID 为 当前Rider 同时 更改相应 Orders 元组的 oStatus 为 “骑手已接单”
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      // 这里不用管 oRiderID 因为可能是上一个骑手接过又放弃的
      String sql = "select oStatus from Orders where OrderID=? ";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, x.getOrderID());
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        switch (rs.getString(1)) {
          case "等待送单":
            sql = "update Orders set oRiderID=?,oStatus=? where OrderID=?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, Bus.currentRider.getRiderID());
            pst.setString(2, "骑手已接单");
            pst.setInt(3, x.getOrderID());
            pst.executeUpdate();
            break;
          case "新建订单":
            throw new BusinessException("用户尚未提交此订单");
          case "骑手已接单":
            throw new BusinessException("此订单已在派送中");
          default:
            System.out.println("订单状态：" + rs.getString(1));
            throw new BusinessException("订单状态异常");
        }
      } else {
        throw new BusinessException("无此订单");
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void riderRun(BeanViewOrder x) throws BusinessException, DbException {
    // 检查相应 Orders 元组的 oRiderID 应为 当前Rider
    // 更改相应 Orders 元组的 oStatus 为 已送达
    // !!! 上面两步可改为一步
    // update Orders set oStatus='...'
    // where OrderID=x.getOrderID() and x.getRiderID==Link.curRider.getRiderID()
    // 为本骑手的历史工作添加一条记录（第二次数据修改，需要事务控制）
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "select oRiderID from Orders where OrderID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, x.getOrderID());
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        if (rs.getInt(1) == Bus.currentRider.getRiderID()) {
          sql = "update Orders set oStatus=? where OrderID=?";
          pst = conn.prepareStatement(sql);
          pst.setString(1, "订单已送达");
          pst.setInt(2, x.getOrderID());
          if (pst.executeUpdate() == 1) {
            System.out.println("增加一条工作记录");

            // 增加工作记录
            sql = "insert into Rider_Work(RiderID,OrderID,rwPayTime,rwPayValue) values(?,?,?,?)";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, x.getRiderID());
            pst.setInt(2, x.getOrderID());
            pst.setString(3, Bus.sdf.format(new Date()));// 骑手订单送达的时间
            double pay = 0;
            switch (Bus.currentRider.getrLevel()) {
              case "新人":
                pay = 2;
                break;
              case "正式骑手":
                pay = 5;
                break;
              case "精英骑手":
                pay = 7;
                break;
              case "王牌骑手":
                pay = 8;
                break;
              default:
                break;
            }
            pst.setDouble(4, pay);
            pst.executeUpdate();
          }
        } else {
          throw new BusinessException("这不是你的订单吖");
        }
      } else {
        throw new BusinessException("无此订单");
      }
      rs.close();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new DbException(e1);
      }
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void riderFail(BeanViewOrder x) throws BusinessException, DbException {
    // 检查相应 Orders 元组的 oRiderID 为 当前Rider
    // 检查相应 Orders 元组的 oStatus 为 “骑手已接单”
    // 更改相应 Orders 元组的 oRiderID 为 空
    // 更改相应 Orders 元组的 oStatus 为 “等待送单”
    // 同样 此处的检查和更新也可以一起做(就是用Where限定来代替检查)
    // 但是为了用户交互、为了能分级报错，还是分开来做
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "select oRiderID,oStatus from Orders where OrderID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, x.getOrderID());
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        if (rs.getInt(1) == Bus.currentRider.getRiderID() && rs.getString(2).equals("骑手已接单")) {
          // 这里不需要把被污染的 oRiderID 改回去
          sql = "update Orders set oStatus=? where OrderID=?";
          pst = conn.prepareStatement(sql);
          pst.setString(1, "等待送单");
          pst.setInt(2, x.getOrderID());
          pst.executeUpdate();
        }
      } else {
        throw new BusinessException("无此订单");
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void userOrderGet(BeanViewOrder x) throws BusinessException, DbException {
    // 检查状态应为 “订单已送达”
    // 更改状态为 “已确认收货”
    // 这次选用 用where代替检查状态的简易写法
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "update Orders set oStatus=? where OrderID=? and oStatus=? ";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, "已确认收货");
      pst.setInt(2, x.getOrderID());
      pst.setString(3, "订单已送达");
      pst.executeUpdate();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }
}
